package com.xzwzz.kotlindemo.utils;

import com.xzwzz.kotlindemo.ImportNewVideoData;
import com.xzwzz.kotlindemo.video.ExcelSourceBean;
import com.xzwzz.kotlindemo.video.VideoDataBean;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class ExcelSplitUtil {
    public static void writeExcel(List<List<String>> dataList, String excelName, String finalXlsxPath) {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(excelName);
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < dataList.size(); i++) {
            createRow(sheet, dataList.get(i), i, style);
        }
        saveToLocal(wb, finalXlsxPath);
    }

    public static void saveToLocal(Workbook wb, String finalXlsxPath) {
        OutputStream out = null;
        try {
            File file = new File(finalXlsxPath);
            if (file.exists()) {
                file.delete();
                file.createNewFile();
            }
            out = new FileOutputStream(finalXlsxPath);
            wb.write(out);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void writeExcel(List<List<String>> list, String finalXlsxPath) {
        try {
            Workbook wb = new XSSFWorkbook();

            Sheet sheet = wb.createSheet("result");
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            for (int i = 0; i < list.size(); i++) {
                createRow(sheet, list.get(i), i, style);
            }
            saveToLocal(wb, finalXlsxPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static void createRow(Sheet sheet, List<String> list, int cloum, CellStyle style) {

        Row r = sheet.createRow(cloum);
        for (int i = 0; i < list.size(); i++) {
            Cell cell = r.createCell(i);
            cell.setCellValue(list.get(i));
            cell.setCellStyle(style);
        }
    }

    private static Workbook getWorkbook(String path) throws IOException {

        Workbook wb = null;
        try (FileInputStream is = new FileInputStream(path)) {
            wb = WorkbookFactory.create(is);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return wb;
    }

    private static List<List<String>> readSheet(Sheet sheet) {
        List<List<String>> lists = new ArrayList<>();
        // 第一行为标题
        for (Row row : sheet) {
            ArrayList<String> list = new ArrayList<>();
            for (Cell cell : row) {
                // 根据不同类型转化成字符串
                cell.setCellType(Cell.CELL_TYPE_STRING);
                list.add(cell.getStringCellValue());
            }
            lists.add(list);
        }
        return lists;
    }

    //自动化填表
    public static void execuExcel(String path, boolean isDir) {
        File file = new File(path);
        if (isDir && file.isDirectory()) {
            for (File f : file.listFiles()) {
                splitExcel(f.getAbsolutePath(), f.isDirectory());
            }
            return;
        }
        try {
            Workbook wb = getWorkbook(path);
            File parent = new File(file.getParent() + "/" + file.getName().substring(0, file.getName().lastIndexOf(".")));
            parent.mkdir();
            List<List<String>> result = new ArrayList<>();
            List<List<String>> finalResult = new ArrayList<>();
            String kemu = convertClass(file.getName());
            String banben;
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                List<List<String>> lists = readSheet(wb.getSheetAt(i));
                banben = wb.getSheetName(i);
                result.clear();
                finalResult.clear();

                finalResult.add(ExcelSourceBean.Companion.getHeaderTitle());
                for (List<String> strings : lists) {
                    if (isInvalidData(strings) == 0) {
                        result.add(strings);
                    }
                }
                try {
                    List<ExcelSourceBean> convertResult =
                            result.stream().map(new Function<List<String>, ExcelSourceBean>() {
                                @Override
                                public ExcelSourceBean apply(List<String> list) {

                                    try {
                                        return new ExcelSourceBean(list);
                                    } catch (Exception e) {
                                        if (list.size() > 2 && list.size() < 15) {
                                            for (int i1 = 0; i1 < 15 - list.size(); i1++) {
                                                list.add(list.size() - 1, "");
                                            }
                                        }
                                        List<String> cache = new ArrayList<>();
                                        cache.addAll(list);
                                        return new ExcelSourceBean(cache);
                                    }
                                }
                            }).collect(Collectors.toList());
                    for (int j = 0; j < convertResult.size(); j++) {
                        ExcelSourceBean bean = convertResult.get(j);
                        finalResult.add(new VideoDataBean(bean.getJuanming(), bean.getZhuanti(), bean.getShipin(),
                                bean.getCategoryTime(), bean.getMingcheng(), bean.getVideoTIme(),
                                bean.getVideoSize(), bean.getTestPoint(), bean.getTeacher(), bean.getDaibiao(), bean.getDaibiaoDesc(),
                                ExcelSourceBean.Companion.getCourseId(kemu, banben), "" + (j + 1), ImportNewVideoData.mYear, kemu, bean.getJuanTime(), bean.getJuanDesc()
                        ).toListString());
                    }
                    writeExcel(finalResult, wb.getSheetName(i), parent.getAbsolutePath() + "/" + wb.getSheetName(i) + ".xlsx");
                } catch (Exception e) {
                    System.out.println("错误的表为" + wb.getSheetName(i));
                    System.out.println(result);
                    e.printStackTrace();
                    continue;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String convertClass(String fileName) {
        if (fileName.contains("语文")) {
            return "语文";
        } else if (fileName.contains("数学")) {
            return "数学";
        } else if (fileName.contains("英语")) {
            return "英语";
        } else if (fileName.contains("政治")) {
            return "政治";
        } else if (fileName.contains("历史")) {
            return "历史";
        } else if (fileName.contains("地理")) {
            return "地理";
        } else if (fileName.contains("生物")) {
            return "生物";
        } else if (fileName.contains("物理")) {
            return "物理";
        } else if (fileName.contains("化学")) {
            return "化学";
        } else {
            return "unknown";
        }
    }

    private static int isInvalidData(List<String> list) {
        for (String s : list) {
            if (s.contains("不可以合并单元格")) {
                return -1;//最上方无用数据栏
            } else if (s.contains("视频大小") || s.contains("视频时长")) {
                return 1;//上边标题栏
            }
        }
        return 0;
    }

    //拆分表
    public static void splitExcel(String path, boolean isDir) {
        File file = new File(path);
        if (isDir && file.isDirectory()) {
            for (File f : file.listFiles()) {
                System.out.println(f.getAbsolutePath());
                splitExcel(f.getAbsolutePath(), f.isDirectory());
            }
            return;
        }
        try {
            // 获取工作薄
            Workbook wb = getWorkbook(path);
            File parent = new File(file.getParent() + "/" + file.getName().substring(0, file.getName().lastIndexOf(".")));
            parent.mkdir();
//            List<List<String>> resultList = new ArrayList<>();
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                List<List<String>> lists = readSheet(wb.getSheetAt(i));
//                if (i != 0) {
//                    resultList.addAll(lists.stream().filter(list -> !isTitle(list) && !isAvailable(list)).collect(Collectors.toList()));
//                } else {
//                    lists.remove(0);
//                    resultList.addAll(lists);
//                }
                writeExcel(lists, "集合", parent.getAbsolutePath() + "/" + wb.getSheetName(i) + ".xlsx");
            }
        } catch (Exception e) {
            System.out.println("当前的表为" + path);
            e.printStackTrace();
        }
    }

    public static void splitExcel(String path) {
        splitExcel(path, false);
    }

    //统计数量
    public static void calcuExcel(String path) {
        Map<String, List<String>> map = new HashMap<>();
        File file = new File(path);
        String savePath;
        if (file.isDirectory()) {
            savePath = file.getAbsolutePath() + "/统计结果.xlsx";
        } else {
            savePath = file.getParent() + "/统计结果.xlsx";
        }
        _calcuExcel(file, map);
        List<List<String>> result = new ArrayList<>();
        result.add(map.get("版本->试卷"));
        for (String s : map.keySet()) {
            if (!s.equals("版本->试卷")) {
                result.add(map.get(s));
            }
        }
        writeExcel(result, savePath);
    }


    static Pattern pattern = Pattern.compile("[(|（]{0,}同[^\\x00-\\xff]{0,}[)|）]{0,}");

    private static int getMaxListLength(Map<String, List<String>> map) {
        int max = 0;
        for (String key : map.keySet()) {
            if (!key.equals("版本->试卷")) {
                if (map.get(key).size() > max) {
                    max = map.get(key).size();
                }
            }
        }
        return max;
    }

    private static void _calcuExcel(File src, Map<String, List<String>> versionMap) {
        int maxSize = getMaxListLength(versionMap);

        try {
            if (src.isFile() && (!src.getName().startsWith("convert_"))&& (!src.getName().contains("结果")) && (src.getName().endsWith(".xlsx") || src.getName().endsWith(".xls"))) {
                System.out.println(src.getAbsolutePath());
                try {
                    // 获取工作薄
                    Workbook wb = getWorkbook(src.getAbsolutePath());
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        //检测第一行是否存在
                        if (!versionMap.containsKey("版本->试卷")) {
                            List<String> list = new ArrayList<>();
                            list.add("版本->试卷");
                            versionMap.put("版本->试卷", list);
                        }

                        String jusnName = src.getName().replace("高考《提分宝典》", "");

                        //放入卷的名字
                        if (!versionMap.get("版本->试卷").contains(jusnName)) {
                            List<String> strings = versionMap.get("版本->试卷");
                            strings.add(jusnName);
                            versionMap.put("版本->试卷", strings);
                        }


                        Matcher matcher = pattern.matcher(wb.getSheetName(i));
                        //版本号
                        String versionName = matcher.replaceAll("").trim();
                        //如果不存在视频版本,则放入视频版本(全国版，北京版)
                        if (!versionMap.containsKey(versionName)) {
                            List<String> list = new ArrayList<>();
                            list.add(versionName);
                            versionMap.put(versionName, list);
                        }


                        //获取当前试卷里的视频数量
                        List<String> shijuans = versionMap.get(versionName);
                        int currentIndex = shijuans.size();
//                        for (int index = currentIndex; index < maxSize - 1; index++) {
//                            shijuans.add("");
//                        }
                        List<List<String>> mSheetVideoList = readSheet(wb.getSheetAt(i));
                        int videoSize = (int) mSheetVideoList.stream().filter(strings -> !isTitle(strings)).count();
                        shijuans.add(videoSize + "");

                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else if (src.isDirectory()) {
                for (File file : Objects.requireNonNull(src.listFiles())) {
                    _calcuExcel(file, versionMap);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        maxSize = getMaxListLength(versionMap);
        for (String key : versionMap.keySet()) {
            List<String> items = versionMap.get(key);
            int length = items.size();
            for (int i = length; i < maxSize; i++) {
                items.add("");
            }
            versionMap.put(key, items);
        }
    }

    //转换还没做的表
    public static void convertExcel(String path, String courseId) {
        File file = new File(path);
        if (file.isDirectory()) {
            System.out.println("just support file,input path is a directory,please try again");
            return;
        }
        try {
            // 获取工作薄
            Workbook wb = getWorkbook(path);
            String fileName = file.getName().substring(0, file.getName().lastIndexOf("."));

            boolean isJiQiao = fileName.contains("技巧");
            String courseName;
            if (isJiQiao) {
                courseName = "技巧";
            } else {
                courseName = ExcelSourceBean.Companion.getCourseName(fileName);
            }

            File parent = new File(file.getParent() + "/" + fileName);
            parent.mkdir();
            List<List<String>> resultList = new ArrayList<>();
            resultList.add(ExcelSourceBean.Companion.getHeaderTitle());
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (wb.getSheetName(i).contains("视频库") || wb.getSheetName(i).contains("普查卷库")) {
                    continue;
                }
                System.out.println("-------------------SheetName:" + wb.getSheetName(i) + "-----------------------");
                List<List<String>> lists = readSheet(wb.getSheetAt(i));
                lists = lists.stream().filter(list -> isAvailable(list)).collect(Collectors.toList());
                for (int m = 0; m < lists.size(); m++) {
                    lists.set(m, replaceSomeElements(lists.get(m), isJiQiao, ExcelSourceBean.Companion.getCourseName(fileName)));
                }
                for (int j = 0; j < lists.size(); j++) {
                    try {
                        VideoDataBean dataBean = new VideoDataBean(new ExcelSourceBean(lists.get(j)), ExcelSourceBean.Companion.getCourseId(courseName, wb.getSheetName(i)), (j + 1) + "", ImportNewVideoData.mYear, courseName);
                        resultList.add(dataBean.toListString());
                    } catch (Exception e) {
                        System.out.println("错误:" + lists.get(j));
                    }
                }
                System.out.println(wb.getSheetName(i) + "有" + lists.size() + "个视频\t当前总数为" + (resultList.size() - 1) + "个");
            }
            writeExcel(resultList, "result", parent.getAbsolutePath() + "/" + fileName + "_converted" + ".xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //转换已经做好的表
    public static void convertExcel(String path) {
        File file = new File(path);
        if (file.isDirectory()) {
            System.out.println("just support file,input path is a directory,please try again");
            return;
        }
        try {
            // 获取工作薄
            Workbook wb = getWorkbook(path);
            String fileName = file.getName().substring(0, file.getName().lastIndexOf("."));
            File parent = new File(file.getParent() + "/" + fileName);
            if (parent.exists()) {
                parent.delete();
            }
            parent.mkdir();
            List<List<String>> resultList = new ArrayList<>();
            resultList.add(ExcelSourceBean.Companion.getHeaderTitle());
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (wb.getSheetName(i).contains("视频库")) {
                    continue;
                }
                System.out.println("-------------------SheetName:" + wb.getSheetName(i) + "-----------------------");
                List<List<String>> lists = readSheet(wb.getSheetAt(i));
                lists = lists.stream().filter(list -> isAvailable(list)).collect(Collectors.toList());
                for (int m = 0; m < lists.size(); m++) {
                    lists.set(m, replaceSomeElements(lists.get(m)));
                }
                resultList.addAll(lists);
                System.out.println(wb.getSheetName(i) + "有" + lists.size() + "个视频\t当前总数为" + (resultList.size() - 1) + "个");
            }
            writeExcel(resultList, "result", parent.getAbsolutePath() + "/" + fileName + "_converted" + ".xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static boolean isAvailable(List<String> list) {
        if (list.size() < 5) {
            return false;
        }
        int count = 0;
        for (String s : list) {
            if (s.trim().isEmpty()) {
                count++;
            }
        }
        return count < 8;
    }

    private static boolean isTitle(List<String> list) {
        for (String s : list) {
            for (String t : titleString) {
                if (s.contains(t)) {
                    return true;
                }
            }
        }
        return false;
    }

    private static List<String> replaceSomeElements(List<String> list, boolean isJiQiao, String courseName) {
        if (isJiQiao) {
            list.set(0, courseName);
        }
        return list;
    }

    private static List<String> replaceSomeElements(List<String> list) {
        for (int i = 0; i < list.size(); i++) {
            list.set(i, list.get(i).replace("全考点普查专题", ""));
        }
        return list;
    }

    private static final String[] titleString = new String[]{
            "编辑填写", "不可合并单元合", "一级标题", "专题时长", "试卷卷名为准"
    };
}
